import os
import sys
import win32com.client as win32

sys.path.append(r"C:\Users\candy\Desktop\testrobot")
from printer import IndentPrinter

printer = IndentPrinter(indent=1)


def main(file_path):
    printer("🚀 开始")
    printer(f"📂 正在检查文件: {file_path}")
    # 检查文件是否存在
    if not os.path.exists(file_path):
        error_message = f"❌ 文件不存在: {file_path}"
        printer(error_message)
        raise Exception(error_message)
    else:
        excel = None
        workbook = None
        try:
            printer("📄 正在打开Excel...")
            excel = win32.gencache.EnsureDispatch("Excel.Application")
            excel.Visible = False
            excel.DisplayAlerts = False
            workbook = excel.Workbooks.Open(file_path)
            printer("🔄 开始刷新数据")
            # 遍历所有工作表
            for sheet in workbook.Sheets:
                try:
                    # 获取工作表中的所有查询
                    queries = sheet.ListObjects
                    if queries.Count > 0:
                        printer(f"[{sheet.Name}]⏳ 正在刷新...")
                        # 刷新所有查询
                        for query in queries:
                            try:
                                # 检查是否有有效的 QueryTable 对象
                                if query.QueryTable:
                                    query.QueryTable.Refresh(BackgroundQuery=False)
                                    printer(f"[{sheet.Name}]✔️ {query.Name}刷新成功。")
                            except Exception:
                                printer(
                                    f"[{sheet.Name}]🚧 {query.Name}没有Power Query查询。"
                                )
                                continue
                    else:
                        printer(f"[{sheet.Name}]🚧 没有Power Query查询。")
                except Exception as e:
                    error_message = f"[{sheet.Name}]🚫 刷新失败。错误: {e}"
                    printer(error_message)
                    raise Exception(error_message)
            else:
                # 所有查询刷新成功，保存并关闭Excel
                workbook.Save()
                printer("👏 所有Power Query查询刷新成功。")
                printer("💾 文件已保存。")

        except Exception as e:
            error_message = f"⛔ 发生错误: {e}"
            printer(error_message)
            raise Exception(error_message)
        finally:
            if workbook:
                workbook.Close(SaveChanges=False)
            if excel:
                excel.Quit()
                printer("✨ 资源已释放。")

            printer("🔚 结束")
